/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package connectionBD;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import projetosustentabilidade.Atributos;
import projetosustentabilidade.Paises;
import projetosustentabilidade.Taxas;

/**
 *
 * @author yuripadua
 */
public class mySQLconsultas {

    private final mySQLconnection con = mySQLconnection.getInstance();
    private Connection connection;

    public mySQLconsultas() {
        connection = con.getConnection();
    }

    public void close(){
        try {
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(mySQLconsultas.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    

    public boolean inserirAtributo(Atributos atributo){

        int idAtributo = atributo.getIdAtributo();
        String nomeAtributo = atributo.getNome();
        String descricaoAtributo = atributo.getDescricao();
        String tipoAtributo = atributo.getClassficacaoTripe();

        try {
            CallableStatement stmt = connection.prepareCall("{call uspInsereAtributo(?, ?, ?, ?)}");            
            stmt.setInt(1, idAtributo);
            stmt.setString(2, nomeAtributo);
            stmt.setString(3, descricaoAtributo);
            stmt.setString(4, tipoAtributo);

            stmt.execute();
            return true;
        } catch (SQLException ex) {
            return false;
        }
    }

    public boolean inserirTaxas(Taxas taxa){

        int idAtributo = taxa.getIdAtributo();
        int idPaises = taxa.getIdPaises();
        float interv01 = taxa.getInterv01();
        float interv02 = taxa.getInterv02();
        float interv03 = taxa.getInterv03();
        float interv04 = taxa.getInterv04();
        float interv05 = taxa.getInterv05();
        float interv06 = taxa.getInterv06();

        try {
            CallableStatement stmt = connection.prepareCall("{call uspInsereTaxas(?, ?, ?, ?, ?, ?, ?, ?)}");
            stmt.setInt(1, idAtributo);
            stmt.setInt(2, idPaises);
            stmt.setFloat(3, interv01);
            stmt.setFloat(4, interv02);
            stmt.setFloat(5, interv03);
            stmt.setFloat(6, interv04);
            stmt.setFloat(7, interv05);
            stmt.setFloat(8, interv06);
            stmt.execute();
            return true;
        } catch (SQLException ex) {
            return false;
        }
    }

    public boolean inserirPaises(Paises pais){

        int idPaises = pais.getIdPaises();
        String continente = pais.getContinente();
        String pai = pais.getPais();
        String info = pais.getInfo();

        try {
            CallableStatement stmt = connection.prepareCall("{call uspInserePaises(?, ?, ?, ?)}");         
            stmt.setInt(1, idPaises);
            stmt.setString(2, continente);
            stmt.setString(3, pai);
            stmt.setString(4, info);

            stmt.execute();
            return true;
        } catch (SQLException ex) {
            return false;
        }
    }

    public String getInfoPais(String nomePais){
        //CALL uspConsultaInfoPaises('Brasil')        
        try {
            CallableStatement stmt = connection.prepareCall("{call uspConsultaInfoPaises(?)}");
            stmt.setString(1, nomePais);
            stmt.execute();
            ResultSet rs = stmt.getResultSet();
            rs.next();
            String infoPais = rs.getString(1);
            return infoPais;
        } catch (SQLException ex) {
            return null;
        }
    }

    public String getDescricaoAtributo(String nomeAtributo){
        //CALL uspConsultaDescricaoAtributo('PIB')
        try {
            CallableStatement stmt = connection.prepareCall("{call uspConsultaDescricaoAtributo(?)}");
            stmt.setString(1, nomeAtributo);
            stmt.execute();
            ResultSet rs = stmt.getResultSet();
            rs.next();
            String descricaoAtributo = rs.getString(1);
            return descricaoAtributo;
        } catch (SQLException ex) {
            return null;
        }
    }

    private ArrayList<retornoBD> preencheArrayListRetornoBD(ResultSet rs) {
        ArrayList<retornoBD> returnBD = new ArrayList<retornoBD>();
        try {
            while (rs.next()) {
                retornoBD aux = new retornoBD(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getFloat(5),
                        rs.getFloat(6), rs.getFloat(7), rs.getFloat(8), rs.getFloat(9), rs.getFloat(10));

                returnBD.add(aux);
            }
            return returnBD;
        } catch (SQLException ex) {            
            return null;
        }
    }
  
    public ArrayList<retornoBD> getTodosIndicesAtributoEspecifico(String nomeAtributo){
        //CALL uspConsultaIndicesAtributoEspecifico('PIB')
        ArrayList<retornoBD> returnBD = new ArrayList<retornoBD>();
        try {
            CallableStatement stmt = connection.prepareCall("{call uspConsultaIndicesAtributoEspecifico(?)}");
            stmt.setString(1, nomeAtributo);
            stmt.execute();

            return preencheArrayListRetornoBD(stmt.getResultSet());

        } catch (SQLException ex) {
            return null;
        }
    }

    public ArrayList<retornoBD> getTodosIndicesContinenteEspecifico(String nomeContinente){
        //CALL uspConsultaIndicesContinenteEspecifico('Asia')
        ArrayList<retornoBD> returnBD = new ArrayList<retornoBD>();
        try {
            CallableStatement stmt = connection.prepareCall("{call uspConsultaIndicesContinenteEspecifico(?)}");
            stmt.setString(1, nomeContinente);
            stmt.execute();

            return preencheArrayListRetornoBD(stmt.getResultSet());

        } catch (SQLException ex) {
            return null;
        }
    }

    public ArrayList<retornoBD> getTodosIndicesPaisEspecifico(String nomePais){
        //CALL uspConsultaIndicesPaisEspecifico('Brasil')
        ArrayList<retornoBD> returnBD;
        try {
            CallableStatement stmt = connection.prepareCall("{call uspConsultaIndicesPaisEspecifico(?)}");
            stmt.setString(1, nomePais);
            stmt.execute();

            return preencheArrayListRetornoBD(stmt.getResultSet());
        } catch (SQLException ex) {
            return null;
        }
    }

    /**
     * Consulta todos os paises de um continente especifico parrado como parametro
     * @param nomeContinente = String Nome do continente
     * @return ArrayList<String> lista dos nomes dos paises
     */
    public ArrayList<String> getConsultaTodosPaisesContinente(String nomeContinente){
        //CALL uspConsultaTodosPaisesContinente('America');
        ArrayList<String> listaPaises = new ArrayList<String>();
        try {
            CallableStatement stmt = connection.prepareCall("{call uspConsultaTodosPaisesContinente(?)}");
            stmt.setString(1, nomeContinente);
            stmt.execute();

            ResultSet rs = stmt.getResultSet();

            while (rs.next()) {
                listaPaises.add(rs.getString(1));
            }
            return listaPaises;
        } catch (SQLException ex) {
            return null;
        }
    }
    


    
    
    
    
    
    
    

  public ArrayList<retornoBD> getAtributoPaisEspecifico(String nomePais, String nomeAtributo){
        //CALL uspAtributoPais('Angola', 'PIB');
        try {
            CallableStatement stmt = connection.prepareCall("{call uspAtributoPaisEspecifico(?, ?)}");
            stmt.setString(1, nomePais);
            stmt.setString(2, nomeAtributo);
            stmt.execute();            
            return preencheArrayListRetornoBD(stmt.getResultSet());

        } catch (SQLException ex) {            
            return null;
        }
    }

}
